﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using MapCenterSystem.DataInfo;
using MapCenterSystem.Utility;
using MapCenterSystem.DefineType;
using MapCenterSystem.Configuration;

namespace MapCenterSystem.DataAccess
{
    public class PlaceServices
    {
        public IList<PlaceInfo> GetListPlaceInArea(Coordinate start, Coordinate end)
        {
            using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_Place_GetPlaceInArea";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.CommandTimeout = GlobalConfig.TimeOut;
                command.Parameters.AddWithValue("@xLocationStart", start.Latitude);
                command.Parameters.AddWithValue("@yLocationStart", start.Longitude);
                command.Parameters.AddWithValue("@yLocationEnd", end.Longitude);
                command.Parameters.AddWithValue("@xLocationEnd", end.Latitude);
                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                SqlDataReader reader = command.ExecuteReader();
                IList<PlaceInfo> result = new List<PlaceInfo>();
                while (reader.Read())
                {
                    PlaceInfo place = new PlaceInfo();
                    place.Address = Convert.ToString(reader["Address"]);
                    place.Contact = Convert.ToString(reader["Contact"]);
                    place.FullName = Convert.ToString(reader["Fullname"]);
                    place.ID = Convert.ToInt64(reader["ID"]);
                    place.Info = Convert.ToString(reader["Info"]);
                    place.Location = new Coordinate(Convert.ToDouble(reader["xLocation"]), Convert.ToDouble(reader["yLocation"]));
                    place.Phone = Convert.ToString(reader["Phone"]);
                    place.Picture = Convert.ToString(reader["Picture"]);
                    int type = Convert.ToInt32(reader["Type"]);
                    PlaceTypeServices placeTypeService = new PlaceTypeServices();
                    place.Type = placeTypeService.GetPlaceTypeInfo(type);
                    place.Website = Convert.ToString(reader["Website"]);
                    result.Add(place);
                }
                return result;
            }

        }


        public IList<PlaceInfo> GetPlaceByName(String keyword, Coordinate location, int radius)
        {
            using( SqlConnection sqlConnect= new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_Place_getPlaceByName";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.CommandTimeout = GlobalConfig.TimeOut;
                command.Parameters.AddWithValue("@Keyword", keyword);
                command.Parameters.AddWithValue("@xLocation", location.Longitude);
                command.Parameters.AddWithValue("@yLocation", location.Latitude);
                command.Parameters.AddWithValue("@Radius", radius);
                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                SqlDataReader reader = command.ExecuteReader();
                IList<PlaceInfo> result = new List<PlaceInfo>();
                while (reader.Read())
                {
                    PlaceInfo place = new PlaceInfo();
                    place.Address = Convert.ToString(reader["Address"]);
                    place.Contact = Convert.ToString(reader["Contact"]);
                    place.FullName = Convert.ToString(reader["Fullname"]);
                    place.ID = Convert.ToInt64(reader["ID"]);
                    place.Info = Convert.ToString(reader["Info"]);
                    place.Location = new Coordinate(Convert.ToDouble(reader["xLocation"]), Convert.ToDouble(reader["yLocation"]));
                    place.Phone = Convert.ToString(reader["Phone"]);
                    place.Picture = Convert.ToString(reader["Picture"]);
                    int type = Convert.ToInt32(reader["Type"]);
                    PlaceTypeServices placeTypeService = new PlaceTypeServices();
                    place.Type = placeTypeService.GetPlaceTypeInfo(type);
                    place.Website = Convert.ToString(reader["Website"]);
                    result.Add(place);
                }
                return result;
            }
        }

        public IList<PlaceInfo> GetPlaceByPlaceType(int placeType, Coordinate location, int radius)
        {
            using (SqlConnection sqlConnect = new SqlConnection(GlobalConfig.ConnectionString))
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "sp_Place_GetPlaceByPlaceType";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = sqlConnect;
                command.CommandTimeout = GlobalConfig.TimeOut;
                command.Parameters.AddWithValue("@PlaceTypeID", placeType);
                command.Parameters.AddWithValue("@xLocation", location.Latitude);
                command.Parameters.AddWithValue("@yLocation", location.Longitude);
                command.Parameters.AddWithValue("@Radius", radius);
                try
                {
                    sqlConnect.Open();
                }
                catch
                {
                    throw new Exception("Can not connect to database. Please check connect");
                }
                SqlDataReader reader = command.ExecuteReader();
                IList<PlaceInfo> result = new List<PlaceInfo>();
                while (reader.Read())
                {
                    PlaceInfo place = new PlaceInfo();
                    place.Address = Convert.ToString(reader["Address"]);
                    place.Contact = Convert.ToString(reader["Contact"]);
                    place.FullName = Convert.ToString(reader["Fullname"]);
                    place.ID = Convert.ToInt64(reader["ID"]);
                    place.Info = Convert.ToString(reader["Info"]);
                    place.Location = new Coordinate(Convert.ToDouble(reader["xLocation"]), Convert.ToDouble(reader["yxLocation"]));
                    place.Phone = Convert.ToString(reader["Phone"]);
                    place.Picture = Convert.ToString(reader["Picture"]);
                    int type = Convert.ToInt32(reader["Type"]);
                    PlaceTypeServices placeTypeService = new PlaceTypeServices();
                    place.Type = placeTypeService.GetPlaceTypeInfo(type);
                    place.Website = Convert.ToString(reader["Website"]);
                    result.Add(place);
                }
                return result;
            }
        }
        
    }
}
